﻿USE [master]
GO
/****** Object:  Database [sms]    Script Date: 11/25/2010 16:49:23 ******/
CREATE DATABASE [sms] ON  PRIMARY 
( NAME = N'sms', FILENAME = N'C:\短信网关\sms.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'sms_log', FILENAME = N'C:\短信网关\sms_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [sms] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [sms].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [sms] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [sms] SET ANSI_NULLS OFF
GO
ALTER DATABASE [sms] SET ANSI_PADDING OFF
GO
ALTER DATABASE [sms] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [sms] SET ARITHABORT OFF
GO
ALTER DATABASE [sms] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [sms] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [sms] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [sms] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [sms] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [sms] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [sms] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [sms] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [sms] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [sms] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [sms] SET  DISABLE_BROKER
GO
ALTER DATABASE [sms] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [sms] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [sms] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [sms] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [sms] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [sms] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [sms] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [sms] SET  READ_WRITE
GO
ALTER DATABASE [sms] SET RECOVERY FULL
GO
ALTER DATABASE [sms] SET  MULTI_USER
GO
ALTER DATABASE [sms] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [sms] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'sms', N'ON'
GO

USE [sms]
GO
/****** Object:  Table [dbo].[Role]    Script Date: 01/12/2011 15:21:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Role](
	[RoleID] [smallint] IDENTITY(1,1) NOT NULL,
	[RoleName] [nvarchar](20) NOT NULL,
	[RoleValue] [bigint] NOT NULL,
	[Enabled] [bit] NOT NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
	[RoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Department]    Script Date: 01/12/2011 15:21:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department](
	[DepartmentID] [int] IDENTITY(1,1) NOT NULL,
	[DepartmentName] [nvarchar](30) NOT NULL,
	[Priority] [smallint] NOT NULL,
	[RetryTime] [smallint] NOT NULL,
	[Limit] [int] NOT NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
(
	[DepartmentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_Department] UNIQUE NONCLUSTERED 
(
	[DepartmentName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[User]    Script Date: 01/12/2011 15:21:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[User](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[CIL] [nvarchar](30) NOT NULL,
	[NameCN] [nvarchar](30) NOT NULL,
	[NameEN] [nvarchar](30) NOT NULL,
	[Email] [varchar](50) NOT NULL,
	[IsActive] [bit] NOT NULL,
	[DepartmentID] [int] NULL,
	[Mobile] [varchar](20) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_User] UNIQUE NONCLUSTERED 
(
	[CIL] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_User_1] UNIQUE NONCLUSTERED 
(
	[Email] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[UserRoleRelation]    Script Date: 01/12/2011 15:21:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserRoleRelation](
	[UserID] [int] NOT NULL,
	[RoleID] [smallint] NOT NULL,
 CONSTRAINT [PK_RoleUserRelation] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC,
	[RoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Message]    Script Date: 01/12/2011 15:21:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Message](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[BatchID] [int] NOT NULL,
	[UserID] [int] NOT NULL,
	[To] [varchar](20) NOT NULL,
	[Content] [nvarchar](max) NOT NULL,
	[State] [tinyint] NOT NULL,
	[Priority] [smallint] NOT NULL,
	[MaxRetryTime] [smallint] NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[ModifyTime] [datetime] NULL,
	[MessageId] [char](24) NULL,
 CONSTRAINT [PK_sms_queue] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Email]    Script Date: 01/12/2011 15:21:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Email](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NULL,
	[To] [varchar](max) NOT NULL,
	[CC] [varchar](max) NULL,
	[BCC] [varchar](max) NULL,
	[State] [bit] NOT NULL,
	[Content] [nvarchar](max) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[ModifyTime] [datetime] NULL,
	[Subject] [nvarchar](max) NULL,
 CONSTRAINT [PK_Email] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[SMSTemplate]    Script Date: 01/12/2011 15:21:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SMSTemplate](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Content] [nvarchar](max) NULL,
	[UserID] [int] NOT NULL,
 CONSTRAINT [PK_SMSTemplate] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Contact]    Script Date: 01/12/2011 15:21:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contact](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[DisplayName] [nvarchar](30) NOT NULL,
	[UserID] [int] NOT NULL,
	[Enabled] [bit] NOT NULL
 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Reminder]    Script Date: 01/12/2011 15:21:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Reminder](
	[entryid] [varchar](60) NOT NULL,
	[owner] [int] NOT NULL,
	[content] [nvarchar](max) NULL,
	[remindertime] [datetime] NULL,
	[start] [datetime] NULL,
	[end] [datetime] NULL,
	[send] [bit] NOT NULL,
 CONSTRAINT [PK_Reminder] PRIMARY KEY CLUSTERED 
(
	[entryid] ASC,
	[owner] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Number]    Script Date: 01/12/2011 15:21:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Number](
	[Number] [varchar](20) NOT NULL,
	[AddressID] [int] NOT NULL,
 CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED 
(
	[Number] ASC,
	[AddressID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  ForeignKey [FK_Contact_User]    Script Date: 01/12/2011 15:21:48 ******/
ALTER TABLE [dbo].[Contact]  WITH CHECK ADD  CONSTRAINT [FK_Contact_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[User] ([UserID])
GO
ALTER TABLE [dbo].[Contact] CHECK CONSTRAINT [FK_Contact_User]
GO
/****** Object:  ForeignKey [FK_Email_User]    Script Date: 01/12/2011 15:21:48 ******/
ALTER TABLE [dbo].[Email]  WITH CHECK ADD  CONSTRAINT [FK_Email_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[User] ([UserID])
GO
ALTER TABLE [dbo].[Email] CHECK CONSTRAINT [FK_Email_User]
GO
/****** Object:  ForeignKey [FK_Message_User]    Script Date: 01/12/2011 15:21:48 ******/
ALTER TABLE [dbo].[Message]  WITH CHECK ADD  CONSTRAINT [FK_Message_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[User] ([UserID])
GO
ALTER TABLE [dbo].[Message] CHECK CONSTRAINT [FK_Message_User]
GO
/****** Object:  ForeignKey [FK_Number_Contact]    Script Date: 01/12/2011 15:21:48 ******/
ALTER TABLE [dbo].[Number]  WITH CHECK ADD  CONSTRAINT [FK_Number_Contact] FOREIGN KEY([AddressID])
REFERENCES [dbo].[Contact] ([ID])
GO
ALTER TABLE [dbo].[Number] CHECK CONSTRAINT [FK_Number_Contact]
GO
/****** Object:  ForeignKey [FK_Reminder_User]    Script Date: 01/12/2011 15:21:48 ******/
ALTER TABLE [dbo].[Reminder]  WITH CHECK ADD  CONSTRAINT [FK_Reminder_User] FOREIGN KEY([owner])
REFERENCES [dbo].[User] ([UserID])
GO
ALTER TABLE [dbo].[Reminder] CHECK CONSTRAINT [FK_Reminder_User]
GO
/****** Object:  ForeignKey [FK_SMSTemplate_User]    Script Date: 01/12/2011 15:21:48 ******/
ALTER TABLE [dbo].[SMSTemplate]  WITH CHECK ADD  CONSTRAINT [FK_SMSTemplate_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[User] ([UserID])
GO
ALTER TABLE [dbo].[SMSTemplate] CHECK CONSTRAINT [FK_SMSTemplate_User]
GO
/****** Object:  ForeignKey [FK_User_Department]    Script Date: 01/12/2011 15:21:48 ******/
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Department] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[Department] ([DepartmentID])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Department]
GO
/****** Object:  ForeignKey [FK_UserRoleRelation_Role]    Script Date: 01/12/2011 15:21:48 ******/
ALTER TABLE [dbo].[UserRoleRelation]  WITH CHECK ADD  CONSTRAINT [FK_UserRoleRelation_Role] FOREIGN KEY([RoleID])
REFERENCES [dbo].[Role] ([RoleID])
GO
ALTER TABLE [dbo].[UserRoleRelation] CHECK CONSTRAINT [FK_UserRoleRelation_Role]
GO
/****** Object:  ForeignKey [FK_UserRoleRelation_User]    Script Date: 01/12/2011 15:21:48 ******/
ALTER TABLE [dbo].[UserRoleRelation]  WITH CHECK ADD  CONSTRAINT [FK_UserRoleRelation_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[User] ([UserID])
GO
ALTER TABLE [dbo].[UserRoleRelation] CHECK CONSTRAINT [FK_UserRoleRelation_User]
GO
